package edu.uob;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.nio.file.*;
import java.nio.file.attribute.BasicFileAttributes;
import java.util.*;

import java.util.regex.Matcher;
import java.util.regex.Pattern;

import static edu.uob.Select.executeQuery;
import static edu.uob.Select.resultToString;

/*TODO 1. table name database 检测保留字
 *      2. attribute 用正则表达式
 *      3. 测试服务器崩溃的地方
 *      4. id 生成 检测
 *      5. space 思考处理方式
 *
 * */
public class CommandHandle {
    private static final String INSERT_PATTERN = "INSERT INTO ([a-zA-Z0-9_]+) VALUES \\((.+)\\)";
    private static final String VALUE_PATTERN = "'([^']*)'|TRUE|FALSE|\\+?-?\\d+(?:\\.\\d+)?|NULL";
    private static final Pattern INSERT_REGEX = Pattern.compile(INSERT_PATTERN);
    private static final Pattern VALUE_REGEX = Pattern.compile(VALUE_PATTERN);
    public static final String CONDITION_PATTERN = "\\s*(\\()?(\\w+)(==|>|<|>=|<=|!=|LIKE)('([^']*)'|TRUE|FALSE|\\+?-?\\d+(?:\\.\\d+)?|NULL)(\\))?\\s*";
    public static String databaseName = null;
    private static String commandString;
    public static String tableName;
    public static String nameValueList;
    public static String updateCondition;
    public static Path updateTablePath;

    public String parseCommand(String commandString) {
        if (commandString == null || commandString.isEmpty()) {
            throw new IllegalArgumentException("Invalid command syntax. Command is null or empty.");
        }

        // 去除左右空格
        commandString = commandString.trim();
        // 检查是否以分号结尾
        if (commandString.charAt(commandString.length() - 1) != ';') {
            throw new IllegalArgumentException("Invalid command syntax. Missing semicolon at the end.");
        }

        commandString = commandString.substring(0, commandString.length() - 1).trim();

        CommandHandle.commandString = commandString;
        String commandSQL = parseCommandType();

        if (commandSQL != null) {
            return commandSQL;
        } else {
            throw new IllegalArgumentException("Invalid command syntax");
        }
    }

    private String parseCommandType() {
        String[] tokens = commandString.split("\\s+"); // Split command string by whitespace
        String command = tokens[0].toUpperCase(); // Get the first token as the command type

        return switch (command) {
            case "USE" -> handleUseCommand(Arrays.copyOfRange(tokens, 1, tokens.length));
            case "CREATE" -> handleCreateCommand(Arrays.copyOfRange(tokens, 1, tokens.length));
            case "DROP" -> handleDropCommand(Arrays.copyOfRange(tokens, 1, tokens.length));
            case "ALTER" -> handleAlterCommand(Arrays.copyOfRange(tokens, 1, tokens.length));
            case "SELECT" -> handleSelectCommand(Arrays.copyOfRange(tokens, 0, tokens.length));
            case "INSERT" -> handleInsertCommand(Arrays.copyOfRange(tokens, 0, tokens.length));
            case "UPDATE" -> handleUpdateCommand(Arrays.copyOfRange(tokens, 0, tokens.length));
            case "DELETE" -> handleDeleteCommand(Arrays.copyOfRange(tokens, 0, tokens.length));
            case "JOIN" -> handleJoinCommand(Arrays.copyOfRange(tokens, 0, tokens.length));
            default -> throw new IllegalArgumentException("Unsupported command: " + command);
        };
    }

    private String handleJoinCommand(String[] tokens) {
        String result;
        if(tokens.length == 8 && Objects.equals(tokens[2], "AND") && Objects.equals(tokens[4], "ON") && Objects.equals(tokens[6], "AND")){
            String tableName1 = tokens[1];
            String tableName2 = tokens[3];

            String AttributeName1 = tokens[5];
            String AttributeName2 = tokens[7];

            //111111111111111
            Path path1 = getTablePath(tableName1);
            System.out.println("path1 "+path1);
            if (!Files.exists(path1)) {
                throw new IllegalArgumentException( tableName2+" does not exists.");
            }
            DataTable dataTable1 = new DataTable();
            try {
                dataTable1.loadDataFromPath(path1);
            } catch (IOException e) {
                throw new IllegalArgumentException ("Error loading data from file: " + e.getMessage());
            }
            if(!dataTable1.getColumnNames().contains(AttributeName1)){
                throw new IllegalArgumentException( dataTable1.getColumnNames() +" does not exists " + AttributeName1);
            }
            //222222222222222
            Path path2 = getTablePath(tableName2);
            if (!Files.exists(path2)) {
                throw new IllegalArgumentException( tableName2+" does not exists.");
            }
            DataTable dataTable2 = new DataTable();
            try {
                dataTable2.loadDataFromPath(path2);
            } catch (IOException e) {
                throw new IllegalArgumentException ("Error loading data from file: " + e.getMessage());
            }

            if(!dataTable2.getColumnNames().contains(AttributeName2)){
                throw new IllegalArgumentException( dataTable2.getColumnNames() +" does not exists " + AttributeName2);
            }

            result = executeJoinCommand(dataTable1,dataTable2,AttributeName1,AttributeName2);

        }else{
            throw new IllegalArgumentException("Invalid join command");
        }
        return "[OK]\n"+result;
    }

    private String executeJoinCommand(DataTable dataTable1, DataTable dataTable2, String attributeName1, String attributeName2) {
        DataTable result = new DataTable();
        //1.生成新id+列名
        List<String> columnNames1 = dataTable1.getColumnNames();
        String tableName1 = dataTable1.getTableName();
        columnNames1.replaceAll(s -> tableName1 + "." + s);
        System.out.println(columnNames1);

        List<String> columnNames2 = dataTable2.getColumnNames();
        String tableName2 = dataTable2.getTableName();
        columnNames2.replaceAll(s -> tableName2 + "." + s);
        System.out.println(columnNames2);

        // 合并两个列表
        List<String> resultColumnNames = new ArrayList<>();
        resultColumnNames.add("id");
        resultColumnNames.addAll(columnNames1);
        resultColumnNames.addAll(columnNames2);


        String  noName1 = tableName1+"."+"id";
        String  noName2 = tableName2+"."+"id";
        String  noName3 = tableName1+"."+attributeName1;
        String  noName4 = tableName2+"."+attributeName2;

        // 创建一个 List 来存储这四个字符串
        List<String> stringsList = new ArrayList<>();

        // 添加四个字符串到 List 中
        stringsList.add(noName1);
        stringsList.add(noName2);
        stringsList.add(noName3);
        stringsList.add(noName4);
        // 转换为 Set 去除重复元素
        Set<String> stringsSet = new HashSet<>(stringsList);

// 再转回 List
        List<String> uniqueStringsList = new ArrayList<>(stringsSet);
        System.out.println("no "+stringsList);

        for (String s:resultColumnNames){
            result.addColumnName(s);
        }

        int columnIndex1 = dataTable1.getColumnNames().indexOf(noName3);
        int columnIndex2 = dataTable2.getColumnNames().indexOf(noName4);

        //2.循环 两个表数据 是否匹配
        for( Object[] rowData1 : dataTable1.getData()){
            for(Object[] rowData2 : dataTable2.getData()){

                if( rowData1[columnIndex1].equals( rowData2[columnIndex2] )){
                    System.out.println(Arrays.toString(rowData1));
                    System.out.println(Arrays.toString(rowData2));
                    // 创建一个新的数组，长度为两个原始数组的长度之和
                    Object[] rowData3 = new Object[rowData1.length + rowData2.length];

                    System.arraycopy(rowData1, 0, rowData3, 0, rowData1.length);
                    System.arraycopy(rowData2, 0, rowData3, rowData1.length, rowData2.length);

                    StringJoiner joiner = new StringJoiner(",");
                    for (Object obj : rowData3) {
                        joiner.add(obj.toString());
                    }
                    String concatenatedString = joiner.toString();
                    result.addRow(concatenatedString);
                }
            }
        }
        System.out.println("11" + uniqueStringsList);
        System.out.println("22" + result.getColumnNames());

        for (String s:uniqueStringsList){
            result.deleteColumn(s.trim());
        }

        return result.displayDataToString();
    }

    private String handleDeleteCommand(String[] tokens) {

        if(Objects.equals(tokens[1], "FROM") && Objects.equals(tokens[3], "WHERE")){
            String tableName = tokens[2];
            Path deletePath = getTablePath(tableName);

            if (!Files.exists(deletePath)) {
                throw new IllegalArgumentException( tableName+" not exists");
            }

            DataTable dataTable = new DataTable();
            try {
                dataTable.loadDataFromPath(deletePath);
            } catch (IOException e) {
                return ("Error loading data from file: " + e.getMessage());
            }

            //all condition
            String allCondition = String.join(" ", Arrays.copyOfRange(tokens, 4, tokens.length));
            if (isValidCondition(allCondition,dataTable.getColumnNames()).contains("[ERROR]")) {
                return isValidCondition(allCondition,dataTable.getColumnNames());
            }

            executeDeleteCommand(dataTable,allCondition);
            dataTable.writeDataToFile(deletePath);

        }else {
            throw new IllegalArgumentException("Invalid DELETE command. Correct command : DELETE FROM [TableName] WHERE <Condition>");
        }
        return "[Ok] ";
    }

    private void executeDeleteCommand(DataTable dataTable, String allCondition) {

        String cleanedCondition = allCondition.replaceAll("[()]", "");
        cleanedCondition = cleanedCondition.replaceAll("\\s+", "");
        // 使用正则表达式分割字符串为单个条件
        String[] conditions = cleanedCondition.split("(?=(AND|OR))|(?<=(AND|OR))");

        // 初始化存储 AND 和 OR 操作符的数组
        String[] operators = new String[conditions.length];
        // 初始化存储条件的数组
        String[] conditionArray = new String[conditions.length];

        int operatorIndex = 0;
        int conditionIndex = 0;

        for (String str : conditions) {
            if (str.equals("AND") || str.equals("OR")) {
                operators[operatorIndex++] = str;
            } else {
                conditionArray[conditionIndex++] = str;
            }
        }

        if (operatorIndex != conditionIndex - 1) {
            throw new IllegalArgumentException("Number of operators should be one less than number of conditions.");
        }

        conditionArray = Arrays.stream(conditionArray).filter(Objects::nonNull).toArray(String[]::new);

        System.out.println("conditionArray"+Arrays.toString(conditionArray));

        int rowIndex = 0; // 记录满足条件的行数据数量

        for (Object[] rowData : dataTable.getData()) {
            Boolean[] booleanArray = new Boolean[conditionArray.length]; // 初始化布尔数组
            for (int i = 0; i < conditionArray.length; i++) {
                booleanArray[i] = Select.satisfiesCondition(dataTable.getColumnNames(), rowData, conditionArray[i]);
            }
            System.out.println("booleanArray   " + Arrays.toString(booleanArray));

            String restoredExpression = Select.restoreExpression(allCondition, booleanArray);

            if (Select.evaluateExpression(restoredExpression)) {
                // 如果满足条件，则将行数据删除
                dataTable.deleteRow(rowIndex);

                rowIndex--;
            }
            rowIndex++;
        }
    }


    private String handleUpdateCommand(String[] strings) {

        StringJoiner joiner = new StringJoiner(" ");
        for (String str : strings) {
            joiner.add(str);

        }
        String sql = joiner.toString();

        String result = parseUpdate(sql);
        if(!(result ==null)){
            return result;
        }
        exeUpdate(sql);
        return "[OK]";
    }

    private void exeUpdate(String input) {

        DataTable exeUpdateDataTable = new DataTable();

        try {
            exeUpdateDataTable.loadDataFromPath(updateTablePath);
        } catch (IOException e) {
            throw new IllegalArgumentException("[ERROR] Error loading data from file: " + e.getMessage());
        }

        if(input.contains(",")){
            input = input.replaceAll("\\s*,\\s*", ",");
        }
        if(input.contains("=")){
            input = input.replaceAll("\\s*=\\s*", "=");
        }

        String[] tokens = input.split("\\s+");

        StringBuilder allCondition = new StringBuilder();
        for (int i = 5; i < tokens.length; i++) {
            allCondition.append(tokens[i]);
        }

        handleUpdateCondition(exeUpdateDataTable, allCondition.toString());

        exeUpdateDataTable.writeDataToFile(updateTablePath);
    }

    private void handleUpdateCondition(DataTable dataTable, String allCondition) {
        String cleanedCondition = allCondition.replaceAll("[()]", "");
        cleanedCondition = cleanedCondition.replaceAll("\\s+", "");
        // 使用正则表达式分割字符串为单个条件
        String[] conditions = cleanedCondition.split("(?=(AND|OR))|(?<=(AND|OR))");

        // 初始化存储 AND 和 OR 操作符的数组
        String[] operators = new String[conditions.length];
        // 初始化存储条件的数组
        String[] conditionArray = new String[conditions.length];

        int operatorIndex = 0;
        int conditionIndex = 0;

        for (String str : conditions) {
            if (str.equals("AND") || str.equals("OR")) {
                operators[operatorIndex++] = str;
            } else {
                conditionArray[conditionIndex++] = str;
            }
        }

        if (operatorIndex != conditionIndex - 1) {
            throw new IllegalArgumentException("Number of operators should be one less than number of conditions.");
        }

        operators = Arrays.stream(operators).filter(Objects::nonNull).toArray(String[]::new);
        conditionArray = Arrays.stream(conditionArray).filter(Objects::nonNull).toArray(String[]::new);

        String [] nameValueListHandle = nameValueList.split(",");
        int rowIndex = 0;
        for (Object[] rowData : dataTable.getData()) {

            Boolean[] booleanArray = new Boolean[conditionArray.length]; // 初始化布尔数组
            for (int i = 0; i < conditionArray.length; i++) {
                booleanArray[i] = Select.satisfiesCondition(dataTable.getColumnNames(), rowData, conditionArray[i]);
            }

            if (Select.evaluateExpression(operators, booleanArray)) {
                // 如果满足条件，则将保存行索引i

                for (String s : nameValueListHandle) {
                    String[] parts = s.split("=");
                    String updateName = parts[0].trim();
                    int columnIndex = dataTable.getColumnNames().indexOf(updateName);
                    String values = parts[1].trim();
                    values = values.replace("'", "");

                    dataTable.updateData(rowIndex, columnIndex, values);
                }
            }
            rowIndex++;
        }
    }

    public String parseUpdate(String input) {
        if(input.contains(",")){
            input = input.replaceAll("\\s*,\\s*", ",");
        }
        if(input.contains("=")){
            input = input.replaceAll("\\s*=\\s*", "=");
        }


        String[] tokens = input.split("\\s+");

        if (tokens.length >= 5 && tokens[0].equals("UPDATE") && tokens[2].equals("SET") && tokens[4].equals("WHERE")) {
            tableName = tokens[1];
            nameValueList = tokens[3];
            updateCondition = tokens[5];
            updateTablePath = getTablePath(tableName);

            DataTable updateDataTable = new DataTable();
            try {
                updateDataTable.loadDataFromPath(updateTablePath);
            } catch (IOException e) {
                return ("[ERROR] Error loading data from file: " + e.getMessage());
            }
            String nameValueListResult = parseNameValueList(nameValueList,updateDataTable.getColumnNames());
            if (nameValueListResult != null && nameValueListResult.contains("[ERROR]")) {
                return nameValueListResult;
            }
            if (isValidCondition(updateCondition,updateDataTable.getColumnNames()).contains("[ERROR]")) {
                return isValidCondition(updateCondition,updateDataTable.getColumnNames());
            }
        } else {
            return "[ERROR] Update syntax error.";
        }
        return null;
    }

    private String parseNameValueList(String nameValueList,List<String> columnNames) {
        String[] nameValueListSplit = nameValueList.split(",");
        for(String nameValue : nameValueListSplit){
            String parseNameValuePairResult = parseNameValuePair(nameValue,columnNames);
            if (parseNameValuePairResult.contains("[ERROR]")){
                return parseNameValuePairResult;
            }
        }
        return null;
    }

    private String parseNameValuePair(String nameValue,List<String> columnNames) {

        String regex = "^([a-zA-Z]+)\\s*=\\s*('(?:[^'\\\\]|\\\\.)*'|\\b(?:TRUE|FALSE|NULL)\\b|[-+]?(?:\\d+\\.\\d*|\\.\\d+|\\d+))(?:\\s*|\\s*$)";
        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(nameValue);

        if (matcher.matches()) {
            String attributeName = matcher.group(1);
            if(columnNames.contains(attributeName)){
                return "[OK]";
            }else{
                return "[ERROR] "+columnNames +" not including "+ attributeName;
            }
        } else {
            return "[ERROR] Invalid input."+ nameValue;
        }
    }


    private String handleInsertCommand(String[] strings) {

        StringJoiner joiner = new StringJoiner(" ");
        for (String str : strings) {
            joiner.add(str);
        }
        String sql = joiner.toString();

        String result = parseInsert(sql);
        if (!result.contains("[ERROR]")) {
            result = exeInsert(sql, result);
        }
        return result;
    }

    private String exeInsert(String insertSql, String values) {
        String[] tokens = insertSql.split("\\s+");
        String tableName = tokens[2];

        Path tablePath = getTablePath(tableName);

        DataTable dataTable = new DataTable();
        try {
            dataTable.loadDataFromPath(tablePath);
        } catch (IOException e) {
            return ("Error loading data from file: " + e.getMessage());
        }

        dataTable.addRow(values);

        if (dataTable.writeDataToFile(tablePath).contains("[ERROR]")){
            return dataTable.writeDataToFile(tablePath);
        }

        return dataTable.displayDataToString();
    }

    public String parseInsert(String sql) {

        Matcher matcher = INSERT_REGEX.matcher(sql);
        if (matcher.find()) {
            String[] tokens = sql.split("\\s+");
            String tableName = tokens[2];

            Path tablePath = getTablePath(tableName);

                DataTable dataTable = new DataTable();
                try {
                    dataTable.loadDataFromPath(tablePath);
                } catch (IOException e) {
                    return ("Error loading data from file: " + e.getMessage());
                }

                List<String> values = new ArrayList<>();
                String[] valueTokens = matcher.group(2).split(",");

                if ((dataTable.getColumnNames().size() - 1) == valueTokens.length) {
                    for (String token : valueTokens) {
                        String trimmedToken = token.trim();

                        if (!VALUE_REGEX.matcher(trimmedToken).matches()) {
                            return "error: " + trimmedToken;
                        } else {
                            values.add(trimmedToken);
                        }
                    }
                    dataTable.dispose();
                    return String.join(",", values);
                } else {
                    dataTable.dispose();
                    return "[ERROR] Mismatched number of inserts\n" + "ColumnNames:\n" + dataTable.getColumnNames() + "\n" + "Value\n" + Arrays.toString(valueTokens) + "\n";
                }
        }
        return "[ERROR] Invalid SQL statement: " + sql;
    }

    private String handleSelectCommand(String[] tokens) {
        if (tokens.length < 4 || !tokens[0].equalsIgnoreCase("SELECT") || !tokens[2].equalsIgnoreCase("FROM")) {
            return "Syntax Error: Invalid SELECT command format.";
        }

        // Extracting TableName if present
        String tableName;
        tableName = tokens[3];
        Path tablePath = getTablePath(tableName);
        DataTable dataTable = new DataTable();
        try {
            dataTable.loadDataFromPath(tablePath);
        } catch (IOException e) {
            return ("Error loading data from file: " + e.getMessage());
        }
        // Extracting WildAttribList
        String wildAttribList = tokens[1];

        if (!wildAttribList.equals("*") && !parseAttributeList(wildAttribList)) {
            return "Syntax Error: Invalid WildAttribList." + wildAttribList;
        }

        if (!wildAttribList.equals("*")) {
            String[] wildAttributes = wildAttribList.split(",");
            // 遍历wildAttributes中的每个属性，检查是否都包含在columnNames中
            for (String wildAttribute : wildAttributes) {
                // 如果wildAttribute不在columnNames中，则返回false
                if (!dataTable.getColumnNames().contains(wildAttribute.trim())) {
                    return "[ERROR]There are no " + Arrays.toString(wildAttributes) + "in" + dataTable.getColumnNames();
                }
            }
        }

        // Checking if WHERE clause is present
        if (tokens.length > 4 && tokens[4].equalsIgnoreCase("WHERE")) {
            if (tokens.length < 6) {
                return "[ERROR] Syntax Error: WHERE clause missing condition.";
            }
            // Extracting Condition
            String condition = String.join(" ", Arrays.copyOfRange(tokens, 5, tokens.length));
            // Check if the condition is valid

            if (isValidCondition(condition,dataTable.getColumnNames()).contains("[ERROR]")) {
                return isValidCondition(condition,dataTable.getColumnNames());
            }
        }

        Object[][] result = executeQuery(dataTable, tokens);
        return resultToString(result);
    }

    private String isValidCondition(String condition,List<String> columnNames) {

        //检查括号
        if (hasBrackets(condition)) {
            if (!checkBracketCount(condition)) {
                return "[ERROR] " + condition + " Mismatched bracket count！";
            }
        }

        //检查AND OR
        if (condition.contains("AND") || condition.contains("OR")) {
            String[] conditions = condition.split("(?=(AND|OR))|(?<=(AND|OR))");
            // 初始化存储条件的数组
            String[] conditionArray = new String[conditions.length];

            int conditionIndex = 0;

            for (String str : conditions) {
                if (!str.equals("AND") && !str.equals("OR")) {
                    conditionArray[conditionIndex++] = str;
                }
            }

            for (String s : conditionArray) {
                return parseCondition(s,columnNames);
            }

        } else {
            return parseCondition(condition,columnNames);
        }
        return condition;
    }

    public boolean hasBrackets(String condition) {
        return condition.contains("(") || condition.contains(")");
    }

    public boolean checkBracketCount(String condition) {
        Stack<Character> stack = new Stack<>();
        for (char ch : condition.toCharArray()) {
            if (ch == '(') {
                stack.push(ch);
            } else if (ch == ')') {
                if (stack.isEmpty()) {
                    return false; // 如果没有左括号匹配，返回false
                }
                stack.pop(); // 左括号匹配，出栈
            }
        }
        return stack.isEmpty(); // 检查栈是否为空，为空说明括号数量匹配
    }

    public String parseCondition(String condition,List<String> columnNames) {

        condition = condition.replaceAll("\\s*==\\s*", "==")
                .replaceAll("\\s*>\\s*", ">")
                .replaceAll("\\s*<\\s*", "<")
                .replaceAll("\\s*>=\\s*", ">=")
                .replaceAll("\\s*<=\\s*", "<=")
                .replaceAll("\\s*!=\\s*", "!=")
                .replaceAll("\\s*LIKE\\s*", "LIKE"); // Remove spaces around comparison operators

        Pattern pattern = Pattern.compile(CONDITION_PATTERN);
        Matcher matcher = pattern.matcher(condition);

        boolean found = false; // 标志是否找到匹配的条件

        // 使用 while 循环来查找匹配的子字符串
        while (matcher.find()) {
            found = true; // 找到匹配的条件
            String attributeName = matcher.group(2); // 属性名
            String operator = matcher.group(3); // 操作符
            String value = matcher.group(4); // 值

            System.out.println("Attribute Name: " + attributeName);
            System.out.println("Operator: " + operator);
            System.out.println("Value: " + value);

            if (!columnNames.contains(attributeName)) {
                return "[ERROR]: There are no " + attributeName + " in " + columnNames;
            }
        }

        if (!found) {
            return "[ERROR]: Syntax Error: Invalid condition format." + condition;
        }
        return "[OK] Condition parsed successfully.";
    }


    private String handleAlterCommand(String[] tokens) {
        if (tokens.length < 4) {
            throw new IllegalArgumentException("[ERROR]: Invalid number of tokens for ALTER command.");
        }

        String tableName = tokens[1];
        String attributeName = tokens[3];
        if (Objects.equals(tokens[0], "TABLE")) {
            if (Objects.equals(tokens[2], "ADD")) {
                return addAttribute(tableName, attributeName);
            } else if (Objects.equals(tokens[2], "DROP")) {
                return dropAttribute(tableName, attributeName);
            } else {
                return "[ERROR]";
            }
        } else {
            throw new IllegalArgumentException("[ERROR]: Invalid ALTER command syntax.");
        }
    }

    private String addAttribute(String tableName, String attributeName) {
        Path path = getTablePath(tableName);

            if (Files.exists(path)) {
                try {
                    List<String> lines = Files.readAllLines(path);
                    if (!lines.isEmpty()) {
                        String firstLine = lines.get(0);
                        if (firstLine.contains(attributeName)) {
                            return "[ERROR]: Attribute '" + attributeName + "' already exists.";
                        } else {
                            if (isValidAttributeName(attributeName)) {
                                String updatedFirstLine = firstLine + "\t" + attributeName;
                                lines.set(0, updatedFirstLine);
                                Files.write(path, lines, StandardOpenOption.WRITE, StandardOpenOption.TRUNCATE_EXISTING);
                                return "[OK]";
                            } else {
                                return "[ERROR]: attributeName" + attributeName;
                            }
                        }
                    } else {
                        return "[ERROR]: The file is empty.";
                    }
                } catch (IOException e) {
                    return "[ERROR]: Failed to read/write the file: " + e.getMessage();
                }
            } else {
                return "[ERROR]: Table does not exist.";
            }

    }

    private String dropAttribute(String tableName, String attributeName) {
        Path path = getTablePath(tableName);

            if (Files.exists(path)) {
                try {
                    List<String> lines = Files.readAllLines(path);
                    if (!lines.isEmpty()) {
                        String firstLine = lines.get(0);
                        if (firstLine.contains(attributeName)) {
                            // 更新第一行，移除属性名
                            String[] attributes = firstLine.split("\\s+");
                            List<String> updatedLines = new ArrayList<>(lines.size());
                            StringBuilder updatedFirstLine = new StringBuilder();
                            for (String attr : attributes) {
                                if (!attr.equals(attributeName)) {
                                    updatedFirstLine.append(attr).append("\t");
                                }
                            }
                            updatedLines.add(updatedFirstLine.toString().trim());
                            for (int i = 1; i < lines.size(); i++) {
                                String[] values = lines.get(i).split("\\s+");
                                StringBuilder updatedLine = new StringBuilder();
                                int minLen = Math.min(values.length, attributes.length);
                                for (int j = 0; j < minLen; j++) {
                                    if (!attributes[j].equals(attributeName)) {
                                        updatedLine.append(values[j]).append("\t");
                                    }
                                }
                                updatedLines.add(updatedLine.toString().trim());
                            }
                            Files.write(path, updatedLines, StandardOpenOption.WRITE, StandardOpenOption.TRUNCATE_EXISTING);
                            return "[OK]";
                        } else {
                            return "[ERROR]: Attribute " + attributeName + " does not exist.";
                        }
                    } else {
                        return "[ERROR]: The file is empty.";
                    }
                } catch (IOException e) {
                    return "[ERROR]: Failed to read/write the file: " + e.getMessage();
                }
            } else {
                return "[ERROR]: Table does not exist.";
            }
    }


    private String handleUseCommand(String[] tokens) {
        if (tokens.length != 1) {
            throw new IllegalArgumentException("Invalid USE command syntax. Usage: USE <databaseName>;");
        }
        String dbName = tokens[0];
        Path path = getDatabasePath(dbName);
        if (Files.exists(path)) {
            databaseName = dbName;
            return "[OK]";
        } else {
            return "[ERROR]: Database " + dbName + " does not exist.";
        }
    }

    private String handleCreateCommand(String[] tokens) {
        // Handle CREATE DATABASE command
        if (tokens.length == 2 && tokens[0].equalsIgnoreCase("DATABASE")) {
            String dbName = tokens[1];
            return createDatabase(dbName);
        }
        // Handle CREATE TABLE command
        else if (tokens.length == 2 && tokens[0].equalsIgnoreCase("TABLE")) {
            String tableName = tokens[1];
            return createTable(tableName);
        } else if (tokens.length > 3 && tokens[0].equalsIgnoreCase("TABLE")) {
            String tableName = tokens[1];

            String attributeList = String.join(" ", Arrays.copyOfRange(tokens, 2, tokens.length));

            try {
                return handleAttributes(attributeList, tableName);
            } catch (IOException e) {
                throw new RuntimeException(e);
            }

        } else {
            throw new IllegalArgumentException("Invalid CREATE command syntax.");
        }
    }

    private String handleAttributes(String attributeList, String tableName) throws IOException {

        System.out.println(attributeList);
        if (attributeList.startsWith("(") && attributeList.endsWith(")")) {

            String attributeListDone = attributeList.substring(1, attributeList.length() - 1);

            if (parseAttributeList(attributeListDone)) {
                Path path = getTablePath(tableName);

                    String relativePath = path.toString();
                    // 检查文件是否存在
                    File tableFile = new File(relativePath);
                    if (tableFile.exists()) {
                        return "[ERROR]: Table already exists.";
                    }
                    // 如果文件不存在，则创建表
                    createTable(tableName);
                    // 写入表
                    attributeListDone = "id" + "," + attributeListDone;
                    writeToTable(relativePath, attributeListDone);
                    return "[OK]";

            }
        }
        return "[ERROR]: Syntax error: " + (attributeList.startsWith("(") ? "Invalid attribute list." : "Attribute list should be enclosed in parentheses.");
    }

    public boolean parseAttributeList(String attributeList) {

        String[] attributes = attributeList.split(",");

        for (String attribute : attributes) {
            if (!isValidAttributeName(attribute.trim())) {
                return false;
            }
        }
        return true;
    }

    public boolean isValidAttributeName(String attributeName) {
        return isValidPlainText(attributeName);
    }

    public boolean isValidPlainText(String text) {
        for (char c : text.toCharArray()) {
            if (!Character.isLetterOrDigit(c)) {
                return false;
            }
        }
        return true;
    }

    private String handleDropCommand(String[] tokens) {
        // Handle DROP DATABASE command
        if (tokens.length >= 2 && tokens[0].equalsIgnoreCase("DATABASE")) {
            String dbName = tokens[1];
            return dropDatabase(dbName);
        }
        // Handle DROP TABLE command
        else if (tokens.length >= 2 && tokens[0].equalsIgnoreCase("TABLE")) {
            String tableName = tokens[1];
            return dropTable(tableName);
        } else {
            throw new IllegalArgumentException("Invalid DROP command syntax.");
        }
    }

    private String createDatabase(String dbName) {
        Path path = getDatabasePath(dbName);
        try {
            if (Files.exists(path)) {
                return "[ERROR]: The database already exists";
            }
            Files.createDirectories(path);
            return "[OK]";
        } catch (IOException e) {
            return "[ERROR]: Failed to create the directory: " + e.getMessage();
        }
    }

    private Path getDatabasePath(String dbName) {
        String relativePath = ".." + File.separator + "cw-db" + File.separator + "databases" + File.separator + dbName;
        return Paths.get(relativePath);

    }

    private Path getTablePath(String tableName) {
        String relativePath = ".." + File.separator + "cw-db" + File.separator + "databases" + File.separator + databaseName + File.separator + tableName + ".tab";

        if (isNullOrEmpty(databaseName)) {
            throw new IllegalArgumentException("DatabaseName "+"is null.");
            } else {
            Path path = Paths.get(relativePath);
            if (!Files.exists(path)) {
                // 如果文件不存在，抛出自定义的错误
                throw new IllegalArgumentException(tableName +" does not exist. " + relativePath);
            } else {
                return path;
            }
        }
    }

    public boolean isNullOrEmpty(String str) {
        return str == null || str.isEmpty();
    }

    private String dropDatabase(String dbName) {
        Path path = getDatabasePath(dbName);
        try {
            if (Files.exists(path) && Files.isDirectory(path)) {
                Files.walkFileTree(path, new SimpleFileVisitor<>() {
                    @Override
                    public FileVisitResult visitFile(Path file, BasicFileAttributes attrs) throws IOException {
                        Files.deleteIfExists(file);
                        return FileVisitResult.CONTINUE;
                    }

                    @Override
                    public FileVisitResult postVisitDirectory(Path dir, IOException exc) throws IOException {
                        Files.deleteIfExists(dir);
                        return FileVisitResult.CONTINUE;
                    }
                });
                if (!Files.exists(path)) {
                    return "[OK]";
                } else {
                    return "[ERROR]: Unable to drop database '" + dbName + "'.";
                }
            } else {
                return "[ERROR]: Database '" + dbName + "' does not exist.";
            }
        } catch (IOException e) {
            return "[ERROR]: An error occurred while dropping database '" + dbName + "'.";
        }
    }

    private String dropTable(String tableName) {
        Path path = getTablePath(tableName);

            try {
                if (Files.exists(path)) {
                    Files.delete(path);
                    return "[OK]";
                } else {
                    return "[ERROR]: File does not exist";
                }
            } catch (IOException e) {
                return "[ERROR]: Failed to delete file: " + e.getMessage();
            }
    }

    private void writeToTable(String filename, String content) {
        try {
            try (BufferedWriter writer = new BufferedWriter(new FileWriter(filename, true))) {

                String contentWithoutSpaces = content.replaceAll("\\s+", "");
                String tabSeparatedContent = contentWithoutSpaces.replace(",", "\t");
                writer.write(tabSeparatedContent + "\n");
            }
        } catch (IOException e) {
            // 处理异常
            throw new IllegalArgumentException("Unable to write table file."); // 这里只是简单地打印异常信息，你可以根据实际情况进行处理
        }
    }


    private String createTable(String tableName) {
        Path path = getTablePath(tableName);

            File tableFile = new File(path.toString());
            try {
                if (tableFile.exists()) {
                    throw new IllegalArgumentException("[ERROR]: File already exists.");
                } else if (tableFile.createNewFile()) {
                    return "[OK]";
                } else {
                    throw new IllegalArgumentException("Unable to create table file.");
                }
            } catch (IOException e) {
                throw new IllegalArgumentException("Exception occurred while creating table file.");
            }
    }
}